﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Model.DBHelper
{
    public class SQLHelper
    {
        /// <summary>
        /// 全局唯一实例
        /// </summary>
        private static SQLHelper sqlhelper = null;

        /// <summary>
        /// 创建实例
        /// </summary>
        /// <returns></returns>
        public static SQLHelper CreateInstance()
        {
            if (sqlhelper == null)
            {
                sqlhelper = new SQLHelper();
                return sqlhelper;
            }
            else
            {
                return sqlhelper;
            }
        }
        /// <summary>
        /// 连接字符串
        /// </summary>
        private string conStr = "";
        /// <summary>
        /// 初始化
        /// </summary>
        private SQLHelper()
        {
            conStr = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
        }
        /// <summary>
        /// 执行增删改的Sql语句，
        /// </summary>
        /// <param name="sqlstr">Sql语句</param>
        /// <param name="pms">SqlParameter数组，不需要时不传</param>
        /// <returns>返回受影响的行数</returns>
        public int ExecuteNonQuery(string sqlstr, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(sqlstr, con))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    con.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// 获取首行首列的查询
        /// </summary>
        /// <param name="sqlstr">Sql语句</param>
        /// <param name="pms">SqlParameter数组，不需要时不传</param>
        /// <returns>首行首列的值</returns>
        public object ExecuteScalar(string sqlstr, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(sqlstr, con))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(pms);
                    }
                    con.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }
        /// <summary>
        /// 执行Sql语句返回一个Datatable对象
        /// </summary>
        /// <param name="text">Sql语句或存储过程名称</param>
        /// <param name="ct">要执行的类型Sql语句或存储过程</param>
        /// <param name="param">SqlParameter数组，不需要时不传</param>
        /// <returns>Datatable对象</returns>
        public DataTable ExecuteTable(string text, CommandType ct, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(conStr))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter(text, con))
                {
                    if (pms != null)
                    {
                        sda.SelectCommand.CommandType = ct;
                        sda.SelectCommand.Parameters.AddRange(pms);
                    }
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    return dt;
                }
            }
        }

        /// <summary>
        ///  执行存储过程
        /// </summary>
        /// <param name="text">存储过程名称</param>
        /// <param name="pms">参数数组</param>
        /// <param name="index">输出参数索引</param>
        /// <param name="msg">输出信息</param>
        /// <returns>返回bool数据</returns>
        public bool ExecuteResult(string text, SqlParameter[] pms, int indexerror, int indexrow, out string msg)
        {
            using (SqlConnection con = new SqlConnection(conStr))
            {
                int result = 0;
                con.Open();
                using (SqlCommand cmd = new SqlCommand(text, con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (pms != null && pms.Length > 0)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    cmd.ExecuteNonQuery();
                    msg = cmd.Parameters[indexerror].Value.ToString();
                    result = Convert.ToInt32(cmd.Parameters[indexrow].Value.ToString());
                    return result > 0;
                }
            }
        }

        /// <summary>
        /// 执行Sql语句查询信息
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="pms">可变参数</param>
        /// <returns></returns>
        public DataTable ExecuteTableSql(string sql, params SqlParameter[] pms)
        {
            return ExecuteTable(sql, CommandType.Text, pms);
        }
        /// <summary>
        /// 执行存储过程查询信息
        /// </summary>
        /// <param name="Sql">存储过程名称</param>
        /// <param name="pms">可变参数</param>
        /// <returns></returns>
        public DataTable ExecuteTableSP(string sp, params SqlParameter[] pms)
        {
            return ExecuteTable(sp, CommandType.StoredProcedure, pms);
        }
        /// <summary>
        /// 执行多条Sql语句，实现数据库事务。
        /// </summary>
        /// <param name="SqlStringList">多条Sql语句</param>	
        /// <returns>是否执行成功</returns>
        public bool ExecuteSqlTran(List<String> SqlStringList)
        {
            bool b = false;
            using (SqlConnection con = new SqlConnection(conStr))
            {
                con.Open();
                using (SqlTransaction trans = con.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.Transaction = trans;
                    try
                    {
                        int count = 0;//回滚标识
                        //循环
                        foreach (string sql in SqlStringList)
                        {
                            cmd.CommandText = sql;
                            if (cmd.ExecuteNonQuery() < 0)
                            {
                                count = count + 1;
                            }
                        }
                        if (count == 0)
                        {
                            trans.Commit();
                            b = true;
                        }
                        else
                        {
                            trans.Rollback();// 回滚
                            b = false;
                        }
                    }
                    catch
                    {
                        trans.Rollback();// 回滚
                        throw;
                    }
                }
            }
            return b;
        }
        /// <summary>
        /// 执行多条Sql语句，实现数据库事务。
        /// </summary>
        /// <param name="SqlStringList">多条Sql语句</param>	
        /// <param name="listpa">占位符参数</param>
        /// <returns>是否执行成功</returns>
        public bool ExecuteSqlTran(List<String> SqlStringList, List<SqlParameter[]> listpa)
        {
            bool b = false;
            using (SqlConnection con = new SqlConnection(conStr))
            {
                con.Open();
                using (SqlTransaction trans = con.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.Transaction = trans;
                    try
                    {
                        int count = 0;//回滚标识
                        //循环
                        for (int i = 0; i < SqlStringList.Count; i++)
                        {
                            cmd.Parameters.Clear();
                            cmd.CommandText = SqlStringList[i].ToString();
                            if (listpa[i] != null)
                            {
                                cmd.Parameters.AddRange(listpa[i]);
                            }
                            if (cmd.ExecuteNonQuery() < 0)
                            {
                                count = count + 1;
                            }
                        }
                        if (count == 0)
                        {
                            trans.Commit();
                            b = true;
                        }
                        else
                        {
                            trans.Rollback();// 回滚
                            b = false;
                        }
                    }
                    catch
                    {
                        trans.Rollback();// 回滚
                        throw;
                    }
                }
            }
            return b;
        }

        /// <summary>
        ///  向数据库添加一条记录，返回成功标志(bool类型的数据)
        /// </summary>
        /// <typeparam name="T">泛型数据</typeparam>
        /// <param name="model">泛型数据实例</param>
        /// <returns></returns>
        public bool SqlInsert<T>(T model)
        {
            try
            {
                string Tablename = model.GetType().Name;//model的类名称，也是数据库的表名称
                string SqlInsert = "insert into " + Tablename + " (";
                string Sqlparam = "";
                List<SqlParameter> pms = new List<SqlParameter>();
                PropertyInfo[] properties = typeof(T).GetProperties();
                foreach (PropertyInfo p in properties)
                {
                    object result = p.GetValue(model, null);
                    Type type = p.PropertyType;
                    //判断值类型数据的null值
                    if (type.ToString().Contains("Nullable"))
                    {
                        if (result != null)
                        {
                            SqlInsert += p.Name + ",";
                            Sqlparam += "@" + p.Name + ",";
                            if (type.ToString() == "System.Nullable`1[System.Int32]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Int) { Value = Convert.ToInt32(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.Int64]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.BigInt) { Value = Convert.ToInt64(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.String]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.VarChar) { Value = Convert.ToString(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.Decimal]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Decimal) { Value = Convert.ToDecimal(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.Boolean]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Bit) { Value = Convert.ToByte(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.DateTime]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.DateTime) { Value = Convert.ToDateTime(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.Double]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Float) { Value = Convert.ToDouble(result) });
                            }
                        }
                    }
                    else
                    {
                        if (result != null)
                        {
                            SqlInsert += p.Name + ",";
                            Sqlparam += "@" + p.Name + ",";
                            if (type.ToString() == "System.Int32")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Int) { Value = Convert.ToInt32(result) });
                            }
                            if (type.ToString() == "System.Int64")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.BigInt) { Value = Convert.ToInt64(result) });
                            }
                            if (type.ToString() == "System.String")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.VarChar) { Value = Convert.ToString(result) });
                            }
                            if (type.ToString() == "System.Decimal")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Decimal) { Value = Convert.ToDecimal(result) });
                            }
                            if (type.ToString() == "System.Boolean")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Bit) { Value = Convert.ToByte(result) });
                            }
                            if (type.ToString() == "System.DateTime")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.DateTime) { Value = Convert.ToDateTime(result) });
                            }
                            if (type.ToString() == "System.Double")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Float) { Value = Convert.ToDouble(result) });
                            }
                        }
                    }
                }
                SqlInsert = SqlInsert.Substring(0, SqlInsert.Length - 1) + ") values (" + Sqlparam.Substring(0, Sqlparam.Length - 1) + ")";
                SqlParameter[] praparam = pms.ToArray();
                return ExecuteNonQuery(SqlInsert, praparam) > 0;
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 通过事物的方式向数据库添加N条记录,list集合内的object类型必须为model定义的类型   √
        /// </summary>
        /// <param name="objs"></param>
        /// <returns></returns>
        public bool SqlInsertByTrans(List<object> objs)
        {
            try
            {
                List<string> insertsqls = new List<string>();
                foreach (object obj in objs)
                {
                    string TableName = obj.GetType().Name;
                    Type typeobj = obj.GetType();
                    string SqlInsert = "insert into " + TableName + " (";
                    string Sqlparam = "";
                    List<SqlParameter> pms = new List<SqlParameter>();
                    PropertyInfo[] properties = typeobj.GetProperties();
                    foreach (PropertyInfo p in properties)
                    {
                        object result = p.GetValue(obj, null);
                        Type type = p.PropertyType;
                        //判断值类型数据的null值
                        if (type.ToString().Contains("Nullable"))
                        {
                            if (result != null)
                            {
                                SqlInsert += p.Name + ",";
                                if (type.ToString() == "System.Nullable`1[System.Int32]")
                                {
                                    Sqlparam += Convert.ToInt32(result) + ",";
                                }
                                if (type.ToString() == "System.Nullable`1[System.Int64]")
                                {
                                    Sqlparam += Convert.ToInt64(result) + ",";
                                }
                                if (type.ToString() == "System.Nullable`1[System.String]")
                                {
                                    Sqlparam += "'" + Convert.ToString(result) + "',";
                                }
                                if (type.ToString() == "System.Nullable`1[System.Decimal]")
                                {
                                    Sqlparam += Convert.ToDecimal(result) + ",";
                                }
                                if (type.ToString() == "System.Nullable`1[System.Boolean]")
                                {
                                    Sqlparam += Convert.ToByte(result) + ",";
                                }
                                if (type.ToString() == "System.Nullable`1[System.DateTime]")
                                {
                                    Sqlparam += "CONVERT(varchar(20), '" + Convert.ToString(result) + "', 120),";
                                }
                                if (type.ToString() == "System.Nullable`1[System.Double]")
                                {
                                    Sqlparam += Convert.ToDouble(result) + ",";
                                }
                            }
                        }
                        else
                        {
                            if (result != null)
                            {
                                SqlInsert += p.Name + ",";
                                if (type.ToString() == "System.Int32")
                                {
                                    Sqlparam += Convert.ToInt32(result) + ",";
                                }
                                if (type.ToString() == "System.Int64")
                                {
                                    Sqlparam += Convert.ToInt64(result) + ",";
                                }
                                if (type.ToString() == "System.String")
                                {
                                    Sqlparam += "'" + Convert.ToString(result) + "',";
                                }
                                if (type.ToString() == "System.Decimal")
                                {
                                    Sqlparam += Convert.ToDecimal(result) + ",";
                                }
                                if (type.ToString() == "System.Boolean")
                                {
                                    Sqlparam += Convert.ToByte(result) + ",";
                                }
                                if (type.ToString() == "System.DateTime")
                                {
                                    Sqlparam += "CONVERT(varchar(20), '" + Convert.ToString(result) + "', 120),";
                                }
                                if (type.ToString() == "System.Double")
                                {
                                    Sqlparam += Convert.ToDouble(result) + ",";
                                }
                            }
                        }
                    }
                    insertsqls.Add(SqlInsert.Substring(0, SqlInsert.Length - 1) + ") values (" + Sqlparam.Substring(0, Sqlparam.Length - 1) + ")");
                }
                return ExecuteSqlTran(insertsqls);
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 根据T model和条件func更新数据
        /// </summary>
        /// <typeparam name="T">泛型数据</typeparam>
        /// <param name="model">泛型数据实例</param>
        /// <param name="func">lamdba表达式</param>
        /// <returns></returns>
        public bool SqlUpdate<T>(T model, Expression<Func<T, bool>> func)
        {
            ResolveExpression ExpRes = new ResolveExpression();
            try
            {
                string Tablename = model.GetType().Name;//model的类名称，也是数据库的表名称
                string SqlInsert = "Update " + Tablename + " set ";
                List<SqlParameter> pms = new List<SqlParameter>();
                PropertyInfo[] properties = typeof(T).GetProperties();
                foreach (PropertyInfo p in properties)
                {
                    object result = p.GetValue(model, null);
                    Type type = p.PropertyType;
                    //判断值类型数据的null值
                    if (type.ToString().Contains("Nullable"))
                    {
                        if (result != null)
                        {
                            SqlInsert += p.Name + "=" + "@" + p.Name + ",";
                            if (type.ToString() == "System.Nullable`1[System.Int32]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Int) { Value = Convert.ToInt32(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.Int64]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.BigInt) { Value = Convert.ToInt64(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.String]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.VarChar) { Value = Convert.ToString(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.Decimal]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Decimal) { Value = Convert.ToDecimal(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.Boolean]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Bit) { Value = Convert.ToByte(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.DateTime]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.DateTime) { Value = Convert.ToDateTime(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.Double]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Float) { Value = Convert.ToDouble(result) });
                            }
                        }
                    }
                    else
                    {
                        if (result != null)
                        {
                            SqlInsert += p.Name + "=" + "@" + p.Name + ",";
                            if (type.ToString() == "System.Int32")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Int) { Value = Convert.ToInt32(result) });
                            }
                            if (type.ToString() == "System.Int64")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.BigInt) { Value = Convert.ToInt64(result) });
                            }
                            if (type.ToString() == "System.String")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.VarChar) { Value = Convert.ToString(result) });
                            }
                            if (type.ToString() == "System.Decimal")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Decimal) { Value = Convert.ToDecimal(result) });
                            }
                            if (type.ToString() == "System.Boolean")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Bit) { Value = Convert.ToByte(result) });
                            }
                            if (type.ToString() == "System.DateTime")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.DateTime) { Value = Convert.ToDateTime(result) });
                            }
                            if (type.ToString() == "System.Double")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Float) { Value = Convert.ToDouble(result) });
                            }
                        }
                    }
                }
                ExpRes.ResolveToSql(func);
                SqlInsert = SqlInsert.Substring(0, SqlInsert.Length - 1) + " where " + ExpRes.SqlWhere;
                pms.AddRange(ExpRes.Paras);
                SqlParameter[] praparam = pms.ToArray();
                return ExecuteNonQuery(SqlInsert, praparam) > 0;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ExpRes.Dispose();
            }
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <typeparam name="T">泛型数据</typeparam>
        /// <param name="tableName">表名称</param>
        /// <param name="fileds">更新字段</param>
        /// <param name="parameters">更新参数</param>
        /// <param name="func">条件</param>
        /// <returns></returns>
        public bool SqlUpdate<T>(string tableName, string[] fileds, object[] parameters, Expression<Func<T, bool>> func)
        {
            ResolveExpression ExpRes = new ResolveExpression();
            try
            {
                if (parameters.Length != fileds.Length)
                {
                    throw new Exception("字段数据长度和参数数组长度不一致");
                }
                if (func == null)
                {
                    throw new Exception("缺少条件");
                }
                string Sqlupdate = "Update " + tableName + " set ";
                List<SqlParameter> pms = new List<SqlParameter>();
                for (int i = 0; i < fileds.Length; i++)
                {
                    if (i < fileds.Length - 1)
                    {
                        Sqlupdate += fileds[i] + "=@" + fileds[i] + ",";
                    }
                    else
                    {
                        Sqlupdate += fileds[i] + "=@" + fileds[i] + " where";
                    }
                }
                for (int i = 0; i < parameters.Length; i++)
                {
                    pms.Add(new SqlParameter("@" + fileds[i], parameters[i]));
                }
                ExpRes.ResolveToSql(func);
                Sqlupdate += ExpRes.SqlWhere;
                pms.AddRange(pms.ToArray());
                return ExecuteNonQuery(Sqlupdate, pms.ToArray()) > 0;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ExpRes.Dispose();
            }
        }

        /// <summary>
        /// 通过事物的方式向数据库添加N条记录,list集合内的object类型必须为model定义的类型
        /// </summary>
        /// <param name="objs"></param>
        /// <param name="listexp"></param>
        /// <returns></returns>
        public bool SqlUpdateByTrans(List<object> objs, List<Expression> listexp)
        {
            ResolveExpression ResExp = new ResolveExpression();
            try
            {
                List<string> updatesqls = new List<string>();
                List<SqlParameter[]> arpms = new List<SqlParameter[]>();
                int i = 0;
                foreach (object obj in objs)
                {
                    string TableName = obj.GetType().Name;
                    Type typeobj = obj.GetType();
                    string SqlUpdate = "Update " + TableName + " set ";
                    List<SqlParameter> pms = new List<SqlParameter>();
                    PropertyInfo[] properties = typeobj.GetProperties();
                    foreach (PropertyInfo p in properties)
                    {
                        object result = p.GetValue(obj, null);
                        Type type = p.PropertyType;
                        //判断值类型数据的null值
                        if (type.ToString().Contains("Nullable"))
                        {
                            if (result != null)
                            {
                                SqlUpdate += p.Name + "=" + "@" + p.Name + ",";
                                if (type.ToString() == "System.Nullable`1[System.Int32]")
                                {
                                    pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Int) { Value = Convert.ToInt32(result) });
                                }
                                if (type.ToString() == "System.Nullable`1[System.Int64]")
                                {
                                    pms.Add(new SqlParameter("@" + p.Name, SqlDbType.BigInt) { Value = Convert.ToInt64(result) });
                                }
                                if (type.ToString() == "System.Nullable`1[System.String]")
                                {
                                    pms.Add(new SqlParameter("@" + p.Name, SqlDbType.VarChar) { Value = Convert.ToString(result) });
                                }
                                if (type.ToString() == "System.Nullable`1[System.Decimal]")
                                {
                                    pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Decimal) { Value = Convert.ToDecimal(result) });
                                }
                                if (type.ToString() == "System.Nullable`1[System.Boolean]")
                                {
                                    pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Bit) { Value = Convert.ToByte(result) });
                                }
                                if (type.ToString() == "System.Nullable`1[System.DateTime]")
                                {
                                    pms.Add(new SqlParameter("@" + p.Name, SqlDbType.DateTime) { Value = Convert.ToDateTime(result) });
                                }
                                if (type.ToString() == "System.Nullable`1[System.Double]")
                                {
                                    pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Float) { Value = Convert.ToDouble(result) });
                                }
                            }
                        }
                        else
                        {
                            if (result != null)
                            {
                                SqlUpdate += p.Name + "=" + "@" + p.Name + ",";
                                if (type.ToString() == "System.Int32")
                                {
                                    pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Int) { Value = Convert.ToInt32(result) });
                                }
                                if (type.ToString() == "System.Int64")
                                {
                                    pms.Add(new SqlParameter("@" + p.Name, SqlDbType.BigInt) { Value = Convert.ToInt64(result) });
                                }
                                if (type.ToString() == "System.String")
                                {
                                    pms.Add(new SqlParameter("@" + p.Name, SqlDbType.VarChar) { Value = Convert.ToString(result) });
                                }
                                if (type.ToString() == "System.Decimal")
                                {
                                    pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Decimal) { Value = Convert.ToDecimal(result) });
                                }
                                if (type.ToString() == "System.Boolean")
                                {
                                    pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Bit) { Value = Convert.ToByte(result) });
                                }
                                if (type.ToString() == "System.DateTime")
                                {
                                    pms.Add(new SqlParameter("@" + p.Name, SqlDbType.DateTime) { Value = Convert.ToDateTime(result) });
                                }
                                if (type.ToString() == "System.Double")
                                {
                                    pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Float) { Value = Convert.ToDouble(result) });
                                }
                            }
                        }
                    }

                    ResExp.ResolveToSql(listexp[i]);
                    pms.AddRange(ResExp.Paras);
                    updatesqls.Add(SqlUpdate.Substring(0, SqlUpdate.Length - 1) + " where " + ResExp.SqlWhere);
                    arpms.Add(pms.ToArray());
                    i = i + 1;
                }
                return ExecuteSqlTran(updatesqls, arpms);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ResExp.Dispose();
            }
        }
        /// <summary>
        /// 根据T ,TableName 和条件func更新数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="func"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public bool SqlDelete<T>(Expression<Func<T, bool>> func, string TableName)
        {
            ResolveExpression ExpRes = new ResolveExpression();
            try
            {
                string SqlDelete = string.Empty;
                ExpRes.ResolveToSql(func);
                SqlDelete = "delete from " + TableName + " where " + ExpRes.SqlWhere;
                return ExecuteNonQuery(SqlDelete, ExpRes.Paras) > 0;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ExpRes.Dispose();
            }
        }
        /// <summary>
        /// 根据model生成Insert语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        private string CreateInsertSql<T>(T model, out SqlParameter[] param)
        {
            try
            {
                string Tablename = model.GetType().Name;//model的类名称，也是数据库的表名称
                string SqlInsert = "insert into " + Tablename + " (";
                string Sqlparam = "";
                List<SqlParameter> pms = new List<SqlParameter>();
                PropertyInfo[] properties = typeof(T).GetProperties();
                foreach (PropertyInfo p in properties)
                {
                    Type type = p.PropertyType;
                    object result = p.GetValue(model, null);
                    if (type.ToString().Contains("Nullable"))
                    {
                        if (result != null)
                        {
                            SqlInsert += p.Name + ",";
                            Sqlparam += "@" + p.Name + ",";
                            if (type.ToString() == "System.Nullable`1[System.Int32]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Int) { Value = Convert.ToInt32(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.Int64]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.BigInt) { Value = Convert.ToInt64(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.String]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.VarChar) { Value = Convert.ToString(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.Decimal]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Decimal) { Value = Convert.ToDecimal(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.Boolean]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Bit) { Value = Convert.ToByte(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.DateTime]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.DateTime) { Value = Convert.ToDateTime(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.Double]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Float) { Value = Convert.ToDouble(result) });
                            }
                        }
                    }
                    else
                    {
                        if (result != null)
                        {
                            SqlInsert += p.Name + ",";
                            Sqlparam += "@" + p.Name + ",";
                            if (type.ToString() == "System.Int32")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Int) { Value = Convert.ToInt32(result) });
                            }
                            if (type.ToString() == "System.Int64")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.BigInt) { Value = Convert.ToInt64(result) });
                            }
                            if (type.ToString() == "System.String")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.VarChar) { Value = Convert.ToString(result) });
                            }
                            if (type.ToString() == "System.Decimal")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Decimal) { Value = Convert.ToDecimal(result) });
                            }
                            if (type.ToString() == "System.Boolean")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Bit) { Value = Convert.ToByte(result) });
                            }
                            if (type.ToString() == "System.DateTime")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.DateTime) { Value = Convert.ToDateTime(result) });
                            }
                            if (type.ToString() == "System.Double")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Float) { Value = Convert.ToDouble(result) });
                            }
                        }
                    }
                }
                param = pms.ToArray();
                return SqlInsert.Substring(0, SqlInsert.Length - 1) + ") values (" + Sqlparam.Substring(0, Sqlparam.Length - 1) + ")";
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 根据T model lambda表达式生成Update语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <param name="func"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        private string CreateUpdateSql<T>(T model, Expression<Func<T, bool>> func, out SqlParameter[] param)
        {
            try
            {
                string Tablename = model.GetType().Name;//model的类名称，也是数据库的表名称
                string SqlInsert = "Update " + Tablename + " set ";
                List<SqlParameter> pms = new List<SqlParameter>();
                PropertyInfo[] properties = typeof(T).GetProperties();
                foreach (PropertyInfo p in properties)
                {
                    object result = p.GetValue(model, null);
                    Type type = p.PropertyType;
                    //判断值类型数据的null值
                    if (type.ToString().Contains("Nullable"))
                    {
                        if (result != null)
                        {
                            SqlInsert += p.Name + "=" + "@" + p.Name + ",";
                            if (type.ToString() == "System.Nullable`1[System.Int32]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Int) { Value = Convert.ToInt32(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.Int64]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.BigInt) { Value = Convert.ToInt64(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.String]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.VarChar) { Value = Convert.ToString(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.Decimal]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Decimal) { Value = Convert.ToDecimal(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.Boolean]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Bit) { Value = Convert.ToByte(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.DateTime]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.DateTime) { Value = Convert.ToDateTime(result) });
                            }
                            if (type.ToString() == "System.Nullable`1[System.Double]")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Float) { Value = Convert.ToDouble(result) });
                            }
                        }
                    }
                    else
                    {
                        if (result != null)
                        {
                            SqlInsert += p.Name + "=" + "@" + p.Name + ",";
                            if (type.ToString() == "System.Int32")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Int) { Value = Convert.ToInt32(result) });
                            }
                            if (type.ToString() == "System.Int64")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.BigInt) { Value = Convert.ToInt64(result) });
                            }
                            if (type.ToString() == "System.String")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.VarChar) { Value = Convert.ToString(result) });
                            }
                            if (type.ToString() == "System.Decimal")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Decimal) { Value = Convert.ToDecimal(result) });
                            }
                            if (type.ToString() == "System.Boolean")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Bit) { Value = Convert.ToByte(result) });
                            }
                            if (type.ToString() == "System.DateTime")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.DateTime) { Value = Convert.ToDateTime(result) });
                            }
                            if (type.ToString() == "System.Double")
                            {
                                pms.Add(new SqlParameter("@" + p.Name, SqlDbType.Float) { Value = Convert.ToDouble(result) });
                            }
                        }
                    }
                }
                ResolveExpression ExpRes = new ResolveExpression();
                ExpRes.ResolveToSql(func);
                SqlInsert = SqlInsert.Substring(0, SqlInsert.Length - 1) + " where " + ExpRes.SqlWhere;
                pms.AddRange(ExpRes.Paras);
                param = pms.ToArray();
                return SqlInsert;
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 创建DeleteSql语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="func">lambda表达式</param>
        /// <param name="TableName">表名称</param>
        /// <param name="param"></param>
        /// <returns></returns>
        private string CreateDeleteSql<T>(Expression<Func<T, bool>> func, string TableName, out SqlParameter[] param)
        {
            string SqlDelete = string.Empty;
            ResolveExpression ExpRes = new ResolveExpression();
            try
            {
                ExpRes.ResolveToSql(func);
                SqlDelete = "delete from " + TableName + " where " + ExpRes.SqlWhere;
                param = ExpRes.Paras;
                return SqlDelete;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                ExpRes.Dispose();
            }
        }
        /// <summary>
        /// 根据T model查询数据 返回T 对象
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="func">lamdba表达式</param>
        /// <param name="TableName">表名称</param>
        /// <returns></returns>
        public T SqlSelect<T>(Expression<Func<T, bool>> func, string TableName) where T : new()
        {
            ResolveExpression ExpRes = new ResolveExpression();
            try
            {
                T model = new T();
                string SqlSelect = string.Empty;

                ExpRes.ResolveToSql(func);
                SqlSelect = "select * from " + TableName + " where " + ExpRes.SqlWhere;
                SqlParameter[] param = ExpRes.Paras;
                DataTable dt = ExecuteTableSql(SqlSelect, param);
                if (dt != null && dt.Rows.Count > 0)
                {
                    return DrToModel<T>(dt.Rows[0], model);
                }
                else
                {
                    return default(T);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ExpRes.Dispose();
            }
        }
        /// <summary>
        /// 根据T 查询数量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="func"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public int SqlSelectCount<T>(Expression<Func<T, bool>> func, string TableName)
        {
            ResolveExpression ExpRes = new ResolveExpression();
            try
            {
                string SqlSelect = string.Empty;

                ExpRes.ResolveToSql(func);
                SqlSelect = "select count(*) from " + TableName + " where " + ExpRes.SqlWhere;
                SqlParameter[] param = ExpRes.Paras;
                object o = ExecuteScalar(SqlSelect, param);
                return Convert.ToInt32(o);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ExpRes.Dispose();
            }
        }
        /// <summary>
        /// 根据T 查询某一列的最大值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="func">数据筛选条件</param>
        /// <param name="ColumnName">列名</param>
        /// <param name="TableName">表名</param>
        /// <returns></returns>
        public object SqlSelectMaxColumn<T>(Expression<Func<T, bool>> func, string ColumnName, string TableName)
        {
            ResolveExpression ExpRes = new ResolveExpression();
            try
            {
                string SqlSelect = string.Empty;

                ExpRes.ResolveToSql(func);
                SqlSelect = "select max(" + ColumnName + ") from " + TableName + " where " + ExpRes.SqlWhere;
                SqlParameter[] param = ExpRes.Paras;
                object o = ExecuteScalar(SqlSelect, param);
                if (o is DBNull)
                {
                    return null;
                }
                return o;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                ExpRes.Dispose();
            }
        }
        /// <summary>
        /// 根据T 查询某一列的最小值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="func">数据筛选条件</param>
        /// <param name="ColumnName">列名</param>
        /// <param name="TableName">表名</param>
        /// <returns></returns>
        public object SqlSelectMinColumn<T>(Expression<Func<T, bool>> func, string ColumnName, string TableName)
        {
            ResolveExpression ExpRes = new ResolveExpression();
            try
            {
                string SqlSelect = string.Empty;

                ExpRes.ResolveToSql(func);
                SqlSelect = "select min(" + ColumnName + ") from " + TableName + " where " + ExpRes.SqlWhere;
                SqlParameter[] param = ExpRes.Paras;
                object o = ExecuteScalar(SqlSelect, param);
                if (o is DBNull)
                {
                    return null;
                }
                return o;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                ExpRes.Dispose();
            }
        }
        /// <summary>
        /// 根据T 查询数据 返回List集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="func"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public List<T> SqlSelectList<T>(Expression<Func<T, bool>> func, string TableName) where T : new()
        {
            ResolveExpression ExpRes = new ResolveExpression();
            try
            {
                List<T> listT = new List<T>();
                string SqlSelect = string.Empty;

                ExpRes.ResolveToSql(func);
                SqlSelect = "select * from " + TableName + " where " + ExpRes.SqlWhere;
                SqlParameter[] param = ExpRes.Paras;
                DataTable dt = ExecuteTableSql(SqlSelect, param);
                if (dt != null && dt.Rows.Count > 0)
                {
                    T model;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        model = new T();
                        listT.Add(DrToModel<T>(dt.Rows[i], model));
                    }
                }
                else
                {
                    listT = null;
                }
                return listT;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ExpRes.Dispose();
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T">泛型类</typeparam>
        /// <param name="func">linq条件</param>
        /// <param name="TableName">表名</param>
        /// <param name="Order_Field">分页排序字段</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageCount">页容量</param>
        /// <param name="totalCount">输出参数 总页数</param>
        /// <returns></returns>
        public List<T> SqlSelectListByPage<T>(Expression<Func<T, bool>> func, string TableName,string Order_Field, int pageIndex, int pageCount, out int totalCount) where T : new()
        {
            ResolveExpression ExpRes = new ResolveExpression();
            try
            {
                List<T> listT = new List<T>();
                string SqlSelect = string.Empty;
                totalCount = SqlSelectCount<T>(func, TableName);
                ExpRes.ResolveToSql(func);
                SqlSelect = "select * from (select *,ROW_NUMBER() OVER(Order by " + Order_Field + " ) AS Row_Id from " + TableName + " where " + ExpRes.SqlWhere + ") where Row_Id between " + (pageIndex - 1) * pageCount + " and " + pageIndex * pageCount;
                SqlParameter[] param = ExpRes.Paras;
                DataTable dt = ExecuteTableSql(SqlSelect, param);
                if (dt != null && dt.Rows.Count > 0)
                {
                    T model;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        model = new T();
                        listT.Add(DrToModel<T>(dt.Rows[i], model));
                    }
                }
                else
                {
                    listT = null;
                }
                return listT;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ExpRes.Dispose();
            }
        }

        /// <summary>
        /// DataTable.Row to Model
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dr"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        private T DrToModel<T>(DataRow dr, T model)
        {
            DBNull dbnull = DBNull.Value;
            PropertyInfo[] properties = typeof(T).GetProperties();
            foreach (PropertyInfo propertyInfo in properties)
            {
                string value = string.Empty;
                try
                {
                    value = dr[propertyInfo.Name.ToString()] == dbnull ? null : dr[propertyInfo.Name.ToString()].ToString(); //dr[propertyInfo.ToString()].ToString();
                }
                catch
                {
                    value = null;
                }
                Type type = propertyInfo.PropertyType;
                if (type.ToString() == "System.Int32" || type.ToString() == "System.Nullable`1[System.Int32]")
                {
                    int val;
                    int.TryParse(value, out val);
                    propertyInfo.SetValue(model, val, null);
                }
                if (type.ToString() == "System.Nullable`1[System.Int32]" && value == null)
                {
                    propertyInfo.SetValue(model, null, null);
                }
                if (type.ToString() == "System.Int64" || type.ToString() == "System.Nullable`1[System.Int64]")
                {
                    long lval;
                    long.TryParse(value, out lval);
                    propertyInfo.SetValue(model, lval, null);
                }
                if (type.ToString() == "System.Nullable`1[System.Int64]" && value == null)
                {
                    propertyInfo.SetValue(model, null, null);
                }
                if (type.ToString() == "System.String" || type.ToString() == "System.Nullable`1[System.String]")
                {
                    propertyInfo.SetValue(model, value, null);
                }
                if (type.ToString() == "System.Decimal" || type.ToString() == "System.Nullable`1[System.Decimal]")
                {
                    decimal val1;
                    decimal.TryParse(value, out val1);
                    propertyInfo.SetValue(model, val1, null);
                }
                if (type.ToString() == "System.Nullable`1[System.Decimal]" && value == null)
                {
                    propertyInfo.SetValue(model, null, null);
                }
                if (type.ToString() == "System.Boolean" || type.ToString() == "System.Nullable`1[System.Boolean]")
                {
                    bool val2;
                    bool.TryParse(value, out val2);
                    propertyInfo.SetValue(model, val2, null);
                }
                if (type.ToString() == "System.DateTime" || type.ToString() == "System.Nullable`1[System.DateTime]")
                {
                    DateTime dateresult;
                    DateTime.TryParse(value, out dateresult);
                    propertyInfo.SetValue(model, dateresult, null);
                }
                if (type.ToString() == "System.Nullable`1[System.DateTime]" && value == null)
                {
                    propertyInfo.SetValue(model, null, null);
                }
                if (type.ToString() == "System.Double" || type.ToString() == "System.Nullable`1[System.Double]")
                {
                    double dval;
                    double.TryParse(value, out dval);
                    propertyInfo.SetValue(model, dval, null);
                }
                if (type.ToString() == "System.Nullable`1[System.Double]" && value == null)
                {
                    propertyInfo.SetValue(model, null, null);
                }
            }
            return model;
        }
    }
}
